LOOKUP

The LOOKUP function has two syntax forms: vector and array.

The array form of LOOKUP looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the array. Use this form of LOOKUP when the values you want to match are in the first row or column of the array. Use the other form of LOOKUP when you want to specify the location of the column or row.

Show Tip

In general, it's best to use the HLOOKUP or VLOOKUP function instead of the array form of LOOKUP. This form of LOOKUP is provided for compatibility with other spreadsheet programs.

Syntax 2

Array form

LOOKUP(lookup_value,array)

Lookup_value is a value that LOOKUP searches for in an array. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.

If LOOKUP can't find the lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.
If lookup_value is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value.

Array is a range of cells that contains text, numbers, or logical values that you want to compare with lookup_value.

The array form of LOOKUP is very similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for lookup_value in the first row, VLOOKUP searches in the first column, and LOOKUP searches according to the dimensions of array.

If array covers an area that is wider than it is tall (more columns than rows), LOOKUP searches for lookup_value in the first row.
If array is square or is taller than it is wide (more rows than columns), LOOKUP searches in the first column.
With HLOOKUP and VLOOKUP, you can index down or across, but LOOKUP always selects the last value in the row or column.

Important The values in array must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Show How?

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1

2
3

A	B
Formula	Description (Result)
=LOOKUP("C",{"a","b","c","d";1,2,3,4})	Looks up "C" in first row of the array and returns the value in the last row that's in the same column (3)
=LOOKUP("bump",{"a",1;"b",2;"c",3})	Looks up "bump" in first row of the array and returns the value in the last column that's in the same row (2)